package com.dddpeter.app.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.dddpeter.app.entity.Article;
import com.dddpeter.app.entity.Article.ArticleStatus;
import com.dddpeter.app.entity.ArticleWithStats;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ArticleMapper extends BaseMapper<Article> {
    
    @Select("SELECT * FROM articles WHERE author_id = #{authorId}")
    List<Article> findByAuthorId(@Param("authorId") Long authorId);
    
    @Select("SELECT * FROM articles WHERE status = #{status}")
    List<Article> findByStatus(@Param("status") ArticleStatus status);
    
    @Select("SELECT * FROM articles WHERE author_id = #{authorId} AND status = #{status}")
    List<Article> findByAuthorIdAndStatus(@Param("authorId") Long authorId, @Param("status") ArticleStatus status);
    
    @Select("SELECT * FROM articles WHERE title LIKE '%' || #{keyword} || '%' OR content LIKE '%' || #{keyword} || '%'")
    List<Article> findByTitleOrContentContaining(@Param("keyword") String keyword);
    
    @Select("SELECT * FROM articles WHERE status = #{status} ORDER BY created_at DESC")
    List<Article> findPublishedArticlesOrderByCreatedAtDesc(@Param("status") ArticleStatus status);
    
    @Select("SELECT COUNT(*) FROM articles WHERE author_id = #{authorId}")
    Long countByAuthorId(@Param("authorId") Long authorId);
    
    @Select("SELECT MAX(id) FROM articles")
    Long getMaxId();
    
    /**
     * 查询文章列表并包含热度和评论数统计
     */
    @Select("SELECT a.*, " +
            "COALESCE(al.like_count, 0) as like_count, " +
            "COALESCE(c.comment_count, 0) as comment_count " +
            "FROM articles a " +
            "LEFT JOIN (SELECT article_id, COUNT(*) as like_count FROM article_likes GROUP BY article_id) al ON a.id = al.article_id " +
            "LEFT JOIN (SELECT article_id, COUNT(*) as comment_count FROM comments GROUP BY article_id) c ON a.id = c.article_id " +
            "WHERE a.status = #{status} " +
            "ORDER BY a.created_at DESC " +
            "LIMIT #{offset}, #{limit}")
    List<ArticleWithStats> findPublishedArticlesWithStats(@Param("status") ArticleStatus status, 
                                                          @Param("offset") int offset, 
                                                          @Param("limit") int limit);
    
    /**
     * 查询指定分类的文章列表并包含热度和评论数统计
     */
    @Select("SELECT a.*, " +
            "COALESCE(al.like_count, 0) as like_count, " +
            "COALESCE(c.comment_count, 0) as comment_count " +
            "FROM articles a " +
            "LEFT JOIN (SELECT article_id, COUNT(*) as like_count FROM article_likes GROUP BY article_id) al ON a.id = al.article_id " +
            "LEFT JOIN (SELECT article_id, COUNT(*) as comment_count FROM comments GROUP BY article_id) c ON a.id = c.article_id " +
            "WHERE a.status = #{status} AND a.categories LIKE CONCAT('%', #{category}, '%') " +
            "ORDER BY a.created_at DESC " +
            "LIMIT #{offset}, #{limit}")
    List<ArticleWithStats> findArticlesByCategoryWithStats(@Param("status") ArticleStatus status,
                                                           @Param("category") String category,
                                                           @Param("offset") int offset,
                                                           @Param("limit") int limit);
    
    /**
     * 统计已发布文章总数
     */
    @Select("SELECT COUNT(*) FROM articles WHERE status = #{status}")
    Long countPublishedArticles(@Param("status") ArticleStatus status);
    
    /**
     * 统计指定分类的已发布文章总数
     */
    @Select("SELECT COUNT(*) FROM articles WHERE status = #{status} AND categories LIKE CONCAT('%', #{category}, '%')")
    Long countPublishedArticlesByCategory(@Param("status") ArticleStatus status, @Param("category") String category);
}
